package limousine.dbservice;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;
import limousine.vo.RoleSystems;
import limousine.util.Func;


public class RoleSystemsService
{
	private DataSource ds = null;
	private final String[] fill = {"id","role_id","systems_id"};
	private Func tool = new Func();
	
	public RoleSystemsService(){
		//ds = (new Service()).getDataSource();
	}
	public void setDataSource(DataSource ds) {
		this.ds = ds;
	}	
	private void putData(RoleSystems o,int fillId,String value){//put data to device bean
		value = (value == null)? "":value.trim();		
		value = tool.db2UTF(value);
		
		switch(fillId){
		case 0:{ o.setId(value); break;}
		case 1:{ o.setRoleId(value ); break;}
		case 2:{ o.setSystemsId(value); break;}
		}
	}
////////////////////////////////////////////GET
	public RoleSystems getById(String id ) {//have one
		StringBuffer sql = new StringBuffer();
		sql.append("Select * From role_systems Where id = '"+id.trim()+"' ");
		RoleSystems o = new RoleSystems();
		try {
			Connection conn = ds.getConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql.toString());
			if (rs.next()) {						
				for(int i=0;i<fill.length;i++){
					putData(o,i,rs.getString(fill[i]));
				}
			}
			rs.close();
			stmt.close();
			conn.close();
			return o;
		} catch (Exception e) {
			e.printStackTrace();
		}		
		return null;
	}	
	public List<RoleSystems> getAll() {
		StringBuffer sql = new StringBuffer();
		sql.append("Select * From role_systems order by name");
		List<RoleSystems> l = new ArrayList<RoleSystems>();
		try {
			Connection conn = ds.getConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql.toString());
			while (rs.next()) {	
				RoleSystems o = new RoleSystems();
				for(int i=0;i<fill.length;i++){
					putData(o,i,rs.getString(fill[i]));
				}
				l.add(o);
			}
			rs.close();
			stmt.close();
			conn.close();
			return l;
		} catch (Exception e) {
			e.printStackTrace();
		}		
		return null;
	}	
////////////////////////////////////////////GET
	
//////////////////////////////////INSERT
	public boolean insert(RoleSystems o) {
		StringBuffer sql = new StringBuffer();
		sql.append("INSERT INTO role_systems(role_id,systems_id) ");
		sql.append("VALUES (?,?)");
		try {			
			Connection conn = ds.getConnection();
			PreparedStatement stmt = conn.prepareStatement(sql.toString());			
			stmt.setString(1,o.getRoleId());
			stmt.setString(2,o.getSystemsId());
			stmt.executeUpdate();
			stmt.close();
			//conn.commit();
			conn.close();
		} catch(Exception ex) {
			ex.printStackTrace();
			return false;
		}
		return true;
	}
////////////////////////////////// INSERT

///////////////////////////////// UPDATE
	public boolean update(RoleSystems o) {
		StringBuffer sql = new StringBuffer();
		sql.append("UPDATE role_systems ");
		sql.append("SET role_id=?,systems_id=? ");
		sql.append("where id='"+o.getId()+"' ");
		try {			
			Connection conn = ds.getConnection();
			PreparedStatement stmt = conn.prepareStatement(sql.toString());
			stmt.setString(1,o.getRoleId());
			stmt.setString(2,o.getSystemsId());
			stmt.executeUpdate();
			stmt.close();
			//conn.commit();
			conn.close();
		} catch(Exception ex) {
			ex.printStackTrace();
			return false;
		}
		return true;
	}
///////////////////////////////// UPDATE
	
//////////////////////////////////DELETE
	public boolean deleteById(String id) {//delete 
		StringBuffer sql = new StringBuffer();
		sql.append("delete from role_systems where id = '"+id.trim()+"' ");
		try {			
			Connection conn = ds.getConnection();
			PreparedStatement stmt = conn.prepareStatement(sql.toString());
			stmt.executeUpdate();
			stmt.close();
			//conn.commit();
			conn.close();
		} catch(Exception ex) {
			ex.printStackTrace();
			return false;
		}
		return true;
	}	
	public boolean deleteByRoleId(String id) {//delete 
		StringBuffer sql = new StringBuffer();
		sql.append("delete from role_systems where role_id = '"+id.trim()+"' ");
		try {			
			Connection conn = ds.getConnection();
			PreparedStatement stmt = conn.prepareStatement(sql.toString());
			stmt.executeUpdate();
			stmt.close();
			//conn.commit();
			conn.close();
		} catch(Exception ex) {
			ex.printStackTrace();
			return false;
		}
		return true;
	}	
	public boolean deleteBySystemsId(String id) {//delete 
		StringBuffer sql = new StringBuffer();
		sql.append("delete from role_systems where systems_id = '"+id.trim()+"' ");
		try {			
			Connection conn = ds.getConnection();
			PreparedStatement stmt = conn.prepareStatement(sql.toString());
			stmt.executeUpdate();
			stmt.close();
			//conn.commit();
			conn.close();
		} catch(Exception ex) {
			ex.printStackTrace();
			return false;
		}
		return true;
	}	
	public boolean deleteBySQL(String sql) {//use for web
		try {			
			Connection conn = ds.getConnection();
			PreparedStatement stmt = conn.prepareStatement(sql);
			stmt.executeUpdate();
			stmt.close();
			//conn.commit();
			conn.close();
		} catch(Exception ex) {
			ex.printStackTrace();
			return false;
		}
		return true;
	}
//////////////////////////////////DELETE
}